package com.brayan.webapp.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.Subquery;

import com.brayan.webapp.model.Pub;
import com.brayan.webapp.model.PubCategory;
import com.brayan.webapp.model.PubCategory_;
import com.brayan.webapp.model.PubThread;
import com.brayan.webapp.model.PubThread_;
import com.brayan.webapp.model.Pub_;

public class PubThreadRepository {

	@PersistenceContext(unitName = "ccms")
	EntityManager em;

	/**
	 * The a pub thread by its id.
	 * 
	 * @param pubId
	 * @return
	 */
	public PubThread getPubThreadById(Long pubThreadId) {
		PubThread pubThread = em.find(PubThread.class, pubThreadId);
		return pubThread;
	}

	/**
	 * Retrieve all PubThreads
	 * 
	 * @return A list of all pub threads.
	 */
	public List<PubThread> getAllPubThreads() {
		CriteriaBuilder builder = em.getCriteriaBuilder();
		CriteriaQuery<PubThread> criteriaQuery = builder
				.createQuery(PubThread.class);
		Root<PubThread> rootPub = criteriaQuery.from(PubThread.class);
		criteriaQuery.select(rootPub);

		TypedQuery<PubThread> typedQuery = em.createQuery(criteriaQuery);
		List<PubThread> pubThreadList = typedQuery.getResultList();

		return pubThreadList;
	}

	/**
	 * Retrieve all PubThreads belonging to a Pub .
	 * 
	 * @param pubId
	 *            The publication id to match.
	 * 
	 * @return List<Pub> A list of publication threads matching the criterion.
	 */
	public List<PubThread> getPubThreadsByPubId(Long pubId) {
		CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
		CriteriaQuery<PubThread> criteriaQuery = criteriaBuilder
				.createQuery(PubThread.class);

		// Create the FROM
		Root<Pub> rootPub = criteriaQuery.from(Pub.class);
		// Create the WHERE
		criteriaQuery.where(criteriaBuilder.equal(rootPub.get(Pub_.id), pubId));
		// Create the JOIN
		Join<Pub, PubThread> join = rootPub.join(Pub_.pubThreads);
		// Create the SELECT, at last
		CriteriaQuery<PubThread> cq = criteriaQuery.select(join);

		// Apply the ordering
		criteriaQuery.orderBy(
				criteriaBuilder.desc(join.get(PubThread_.dateModified)),
				criteriaBuilder.desc(join.get(PubThread_.dateCreated)));

		TypedQuery<PubThread> typedQuery = em.createQuery(criteriaQuery);
		List<PubThread> pubList = typedQuery.getResultList();

		return pubList;
	}

	/**
	 * Get threads, which belong to the same category as the passed thread.
	 * 
	 * @param threadId
	 * @return Other threads of the same category without the one thread passed.
	 *         <p/>
	 *         This is the SQL subselect query
	 *         <p/>
	 *         use webapp;
	 * 
	 *         SELECT Distinct(pt2.id), pt2.name thread, pc2.id, pc2.name cat
	 *         FROM pubthread pt2 JOIN pub_pubthread ppt2 ON pt2.id =
	 *         ppt2.pubThreads_id JOIN pub p2 ON ppt2.pups_id = p2.id JOIN
	 *         pubcategory pc2 ON p2.pubCategoryId = pc2.id WHERE pt2.id != 1
	 *         and EXISTS ( SELECT DISTINCT(pt.id) FROM pubthread pt JOIN
	 *         pub_pubthread ppt ON pt.id = ppt.pubThreads_id JOIN pub p ON
	 *         ppt.pups_id = p.id JOIN pubcategory pc ON p.pubCategoryId = pc.id
	 *         where pc2.id = pc.id and pt.id = 1 )
	 */
	public List<PubThread> getPubThreadsOfSameCategory(Long threadId) {
		// String queryString =
		// "SELECT new PubThread(pt2 "
		// + "FROM pubthread pt2 	"
		// + "JOIN pub_pubthread ppt2 ON pt2.id = ppt2.pubThreads_id 	"
		// + "JOIN pub p2 ON ppt2.pups_id = p2.id 	"
		// + "JOIN pubcategory pc2 ON p2.pubCategoryId = pc2.id 	"
		// + "WHERE pt2.id != 1 and EXISTS ( 		"
		// + "SELECT DISTINCT(pt.id) "
		// + "FROM pubthread pt 		"
		// + "JOIN pub_pubthread ppt ON pt.id = ppt.pubThreads_id 		"
		// + "JOIN pub p ON ppt.pups_id = p.id 		"
		// + "JOIN pubcategory pc ON p.pubCategoryId = pc.id 		"
		// + "where pc2.id = pc.id and pt.id = 1	)"
		// ;
		CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
		CriteriaQuery<PubThread> mainQuery = criteriaBuilder
				.createQuery(PubThread.class);

		// 1) MainQuery
		// Create the FROM
		Root<PubThread> rootPubThread = mainQuery.from(PubThread.class);
		// Create the JOIN from the first select: join-chaining. You only need the return for ordering. e.g. cq.orderBy(cb.asc(categoryJoin.get(Pub_.title)));
		Join<Pub, PubCategory> categoryJoin = rootPubThread.join(PubThread_.pups).join(Pub_.pubCategory);
		// Create the WHERE
		mainQuery.where(criteriaBuilder.not(criteriaBuilder.equal(rootPubThread.get(PubThread_.id), threadId)));
		// Create the SELECT, at last
		mainQuery.select(rootPubThread).distinct(true);
		
		// 2) Subquery
		Subquery<PubThread> subquery = mainQuery.subquery(PubThread.class); 
		Root<PubThread> rootPubThreadSub = subquery.from(PubThread.class); 
		//subquery.where(criteriaBuilder.equal(rootPubThread.get(PubThread_.id), threadId));
		Join<Pub, PubCategory> categoryJoinSub = rootPubThreadSub.join(PubThread_.pups).join(Pub_.pubCategory);
		subquery.select(rootPubThreadSub);
		
		//Predicate correlatePredicate = criteriaBuilder.equal(rootPubThreadSub.get(PubThread_.id), rootPubThread);
		Predicate correlatePredicate = criteriaBuilder.and(
				//criteriaBuilder.equal(rootPubThreadSub.get(PubThread_.id), rootPubThread),
				criteriaBuilder.equal(categoryJoinSub.get(PubCategory_.id), categoryJoin.get(PubCategory_.id)),
				
				criteriaBuilder.equal(rootPubThreadSub.get(PubThread_.id), threadId)
				);
		subquery.where(correlatePredicate);		
		
		//Predicate correlatePredicate = criteriaBuilder.equal(rootPubThreadSub.get(PubThread_.id), rootPubThread);
		Predicate mainPredicate = criteriaBuilder.and(
				criteriaBuilder.not(criteriaBuilder.equal(rootPubThread.get(PubThread_.id), threadId)),
				criteriaBuilder.exists(subquery)
				);
		//cq.where(criteriaBuilder.exists(subquery));
		mainQuery.where(mainPredicate);
		

		TypedQuery<PubThread> typedQuery = em.createQuery(mainQuery);
		List<PubThread> otherPubThreads = typedQuery.getResultList();

		return otherPubThreads;
	}

	/**
	 * Retrieve all PubThreads belonging to a Pub .
	 * 
	 * @param pubId
	 *            The publication id to match.
	 * 
	 * @return List<Pub> A list of publication threads matching the criterion.
	 */
	public List<PubThread> getPubThreadsNotHavingPubId(Long pubId) {
		CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
		CriteriaQuery<PubThread> criteriaQuery = criteriaBuilder
				.createQuery(PubThread.class);

		// Create the FROM
		Root<Pub> rootPub = criteriaQuery.from(Pub.class);
		// Create the WHERE
		criteriaQuery.where(criteriaBuilder.equal(rootPub.get(Pub_.id), pubId));
		// Create the JOIN
		Join<Pub, PubThread> join = rootPub.join(Pub_.pubThreads);
		// Create the SELECT, at last
		CriteriaQuery<PubThread> cq = criteriaQuery.select(join);

		TypedQuery<PubThread> typedQuery = em.createQuery(criteriaQuery);
		List<PubThread> pubList = typedQuery.getResultList();

		return pubList;
	}
}
